{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window LAG\n",
    "\n",
    "## COVID-19 Data\n",
    "Notes on the data: This data was assembled based on work done by [Rodrigo Pombo](https://github.com/pomber/covid19) based on [John Hopkins University](https://systems.jhu.edu/research/public-health/ncov/), based on [World Health Organisation](https://www.who.int/health-topics/coronavirus). The data was assembled 21st April 2020 - there are no plans to keep this data set up to date."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "# Prerequesites\n",
    "import getpass\n",
    "%load_ext sql\n",
    "pwd = getpass.getpass()\n",
    "# %sql mysql+pymysql://root:$pwd@localhost:3306/sqlzoo\n",
    "%sql postgresql://postgres:$pwd@localhost/sqlzoo\n",
    "%config SqlMagic.displaylimit = 20"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Window Function\n",
    "The SQL Window functions include LAG, LEAD, RANK and NTILE. These functions operate over a \"window\" of rows - typically these are rows in the table that are in some sense adjacent."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introducing the `covid` table\n",
    "\n",
    "The example uses a WHERE clause to show the cases in 'Italy' in March.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn),\n",
    " confirmed, deaths, recovered\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Modify the query to show data from Spain**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "31 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>whn</th>\n",
       "        <th>confirmed</th>\n",
       "        <th>deaths</th>\n",
       "        <th>recovered</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>1.0</td>\n",
       "        <td>84</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>2.0</td>\n",
       "        <td>120</td>\n",
       "        <td>0</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>3.0</td>\n",
       "        <td>165</td>\n",
       "        <td>1</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>4.0</td>\n",
       "        <td>222</td>\n",
       "        <td>2</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>5.0</td>\n",
       "        <td>259</td>\n",
       "        <td>3</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>6.0</td>\n",
       "        <td>400</td>\n",
       "        <td>5</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>7.0</td>\n",
       "        <td>500</td>\n",
       "        <td>10</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>8.0</td>\n",
       "        <td>673</td>\n",
       "        <td>17</td>\n",
       "        <td>30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>9.0</td>\n",
       "        <td>1073</td>\n",
       "        <td>28</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>10.0</td>\n",
       "        <td>1695</td>\n",
       "        <td>35</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>11.0</td>\n",
       "        <td>2277</td>\n",
       "        <td>54</td>\n",
       "        <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>12.0</td>\n",
       "        <td>2277</td>\n",
       "        <td>55</td>\n",
       "        <td>183</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>13.0</td>\n",
       "        <td>5232</td>\n",
       "        <td>133</td>\n",
       "        <td>193</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>14.0</td>\n",
       "        <td>6391</td>\n",
       "        <td>195</td>\n",
       "        <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>15.0</td>\n",
       "        <td>7798</td>\n",
       "        <td>289</td>\n",
       "        <td>517</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>16.0</td>\n",
       "        <td>9942</td>\n",
       "        <td>342</td>\n",
       "        <td>530</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>17.0</td>\n",
       "        <td>11748</td>\n",
       "        <td>533</td>\n",
       "        <td>1028</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>18.0</td>\n",
       "        <td>13910</td>\n",
       "        <td>623</td>\n",
       "        <td>1081</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>19.0</td>\n",
       "        <td>17963</td>\n",
       "        <td>830</td>\n",
       "        <td>1107</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>20.0</td>\n",
       "        <td>20410</td>\n",
       "        <td>1043</td>\n",
       "        <td>1588</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Spain', 1.0, 84, 0, 2),\n",
       " ('Spain', 2.0, 120, 0, 2),\n",
       " ('Spain', 3.0, 165, 1, 2),\n",
       " ('Spain', 4.0, 222, 2, 2),\n",
       " ('Spain', 5.0, 259, 3, 2),\n",
       " ('Spain', 6.0, 400, 5, 2),\n",
       " ('Spain', 7.0, 500, 10, 30),\n",
       " ('Spain', 8.0, 673, 17, 30),\n",
       " ('Spain', 9.0, 1073, 28, 32),\n",
       " ('Spain', 10.0, 1695, 35, 32),\n",
       " ('Spain', 11.0, 2277, 54, 183),\n",
       " ('Spain', 12.0, 2277, 55, 183),\n",
       " ('Spain', 13.0, 5232, 133, 193),\n",
       " ('Spain', 14.0, 6391, 195, 517),\n",
       " ('Spain', 15.0, 7798, 289, 517),\n",
       " ('Spain', 16.0, 9942, 342, 530),\n",
       " ('Spain', 17.0, 11748, 533, 1028),\n",
       " ('Spain', 18.0, 13910, 623, 1081),\n",
       " ('Spain', 19.0, 17963, 830, 1107),\n",
       " ('Spain', 20.0, 20410, 1043, 1588),\n",
       " ('Spain', 21.0, 25374, 1375, 2125),\n",
       " ('Spain', 22.0, 28768, 1772, 2575),\n",
       " ('Spain', 23.0, 35136, 2311, 2575),\n",
       " ('Spain', 24.0, 39885, 2808, 3794),\n",
       " ('Spain', 25.0, 49515, 3647, 5367),\n",
       " ('Spain', 26.0, 57786, 4365, 7015),\n",
       " ('Spain', 27.0, 65719, 5138, 9357),\n",
       " ('Spain', 28.0, 73235, 5982, 12285),\n",
       " ('Spain', 29.0, 80110, 6803, 14709),\n",
       " ('Spain', 30.0, 87956, 7716, 16780),\n",
       " ('Spain', 31.0, 95923, 8464, 19259)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, EXTRACT(DAY FROM whn) whn, confirmed, deaths, recovered\n",
    "  FROM covid\n",
    "    WHERE name='Spain' AND EXTRACT(MONTH FROM whn)=3\n",
    "    ORDER BY whn;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Introducing the LAG function\n",
    "\n",
    "The LAG function is used to show data from the preceding row or the table. When lining up rows the data is partitioned by country name and ordered by the data whn. That means that only data from Italy is considered.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(whn, 1) OVER (PARTITION BY name ORDER BY whn)\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Modify the query to show confirmed for the day before.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "31 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>date_part</th>\n",
       "        <th>confirmed</th>\n",
       "        <th>lag_confirmed</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>1.0</td>\n",
       "        <td>1694</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2.0</td>\n",
       "        <td>2036</td>\n",
       "        <td>1694</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>3.0</td>\n",
       "        <td>2502</td>\n",
       "        <td>2036</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>4.0</td>\n",
       "        <td>3089</td>\n",
       "        <td>2502</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>5.0</td>\n",
       "        <td>3858</td>\n",
       "        <td>3089</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>6.0</td>\n",
       "        <td>4636</td>\n",
       "        <td>3858</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>7.0</td>\n",
       "        <td>5883</td>\n",
       "        <td>4636</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>8.0</td>\n",
       "        <td>7375</td>\n",
       "        <td>5883</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>9.0</td>\n",
       "        <td>9172</td>\n",
       "        <td>7375</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>10.0</td>\n",
       "        <td>10149</td>\n",
       "        <td>9172</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>11.0</td>\n",
       "        <td>12462</td>\n",
       "        <td>10149</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>12.0</td>\n",
       "        <td>12462</td>\n",
       "        <td>12462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>13.0</td>\n",
       "        <td>17660</td>\n",
       "        <td>12462</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>14.0</td>\n",
       "        <td>21157</td>\n",
       "        <td>17660</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>15.0</td>\n",
       "        <td>24747</td>\n",
       "        <td>21157</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>16.0</td>\n",
       "        <td>27980</td>\n",
       "        <td>24747</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>17.0</td>\n",
       "        <td>31506</td>\n",
       "        <td>27980</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>18.0</td>\n",
       "        <td>35713</td>\n",
       "        <td>31506</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>19.0</td>\n",
       "        <td>41035</td>\n",
       "        <td>35713</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>20.0</td>\n",
       "        <td>47021</td>\n",
       "        <td>41035</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Italy', 1.0, 1694, None),\n",
       " ('Italy', 2.0, 2036, 1694),\n",
       " ('Italy', 3.0, 2502, 2036),\n",
       " ('Italy', 4.0, 3089, 2502),\n",
       " ('Italy', 5.0, 3858, 3089),\n",
       " ('Italy', 6.0, 4636, 3858),\n",
       " ('Italy', 7.0, 5883, 4636),\n",
       " ('Italy', 8.0, 7375, 5883),\n",
       " ('Italy', 9.0, 9172, 7375),\n",
       " ('Italy', 10.0, 10149, 9172),\n",
       " ('Italy', 11.0, 12462, 10149),\n",
       " ('Italy', 12.0, 12462, 12462),\n",
       " ('Italy', 13.0, 17660, 12462),\n",
       " ('Italy', 14.0, 21157, 17660),\n",
       " ('Italy', 15.0, 24747, 21157),\n",
       " ('Italy', 16.0, 27980, 24747),\n",
       " ('Italy', 17.0, 31506, 27980),\n",
       " ('Italy', 18.0, 35713, 31506),\n",
       " ('Italy', 19.0, 41035, 35713),\n",
       " ('Italy', 20.0, 47021, 41035),\n",
       " ('Italy', 21.0, 53578, 47021),\n",
       " ('Italy', 22.0, 59138, 53578),\n",
       " ('Italy', 23.0, 63927, 59138),\n",
       " ('Italy', 24.0, 69176, 63927),\n",
       " ('Italy', 25.0, 74386, 69176),\n",
       " ('Italy', 26.0, 80589, 74386),\n",
       " ('Italy', 27.0, 86498, 80589),\n",
       " ('Italy', 28.0, 92472, 86498),\n",
       " ('Italy', 29.0, 97689, 92472),\n",
       " ('Italy', 30.0, 101739, 97689),\n",
       " ('Italy', 31.0, 105792, 101739)]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, EXTRACT(DAY FROM whn), confirmed,\n",
    "  LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS lag_confirmed\n",
    "    FROM covid\n",
    "    WHERE name='Italy' AND EXTRACT(MONTH FROM whn)=3\n",
    "    ORDER BY whn;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### LAG operation\n",
    "\n",
    "Here is the correct query showing the cases for the day before:\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (partition by name ORDER BY whn) AS lag\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "Notice how the values in the LAG column match the value of the row diagonally above and to the left.\n",
    "\n",
    "name | DAY(whn) | confirmed | dbf\n",
    "------|---|------|----------\n",
    "Italy | 1 | **1694** | null\n",
    "Italy | 2 | 2036 | **1694**\n",
    "Italy | 3 | 2502 | 2036\n",
    "Italy | 4 | 3089 | 2502\n",
    "Italy | 5 | **3858** | 3089\n",
    "Italy | 6 | 4636 | **3858**\n",
    "Italy | 7 | 5883 | 4636\n",
    "Italy | 8 | 7375 | 5883\n",
    "Italy | 9 | 9172 | 7375\n",
    "Italy | 10 | 10149 | 9172\n",
    "... | | |"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Number of new cases\n",
    "\n",
    "The number of confirmed case is cumulative - but we can use LAG to recover the number of new cases reported for each day.\n",
    "\n",
    "```sql\n",
    "SELECT name, DAY(whn), confirmed,\n",
    "   LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn)\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND MONTH(whn) = 3\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "**Show the number of new cases for each day, for Italy, for March.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "31 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>date_part</th>\n",
       "        <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>1.0</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2.0</td>\n",
       "        <td>342</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>3.0</td>\n",
       "        <td>466</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>4.0</td>\n",
       "        <td>587</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>5.0</td>\n",
       "        <td>769</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>6.0</td>\n",
       "        <td>778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>7.0</td>\n",
       "        <td>1247</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>8.0</td>\n",
       "        <td>1492</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>9.0</td>\n",
       "        <td>1797</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>10.0</td>\n",
       "        <td>977</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>11.0</td>\n",
       "        <td>2313</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>12.0</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>13.0</td>\n",
       "        <td>5198</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>14.0</td>\n",
       "        <td>3497</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>15.0</td>\n",
       "        <td>3590</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>16.0</td>\n",
       "        <td>3233</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>17.0</td>\n",
       "        <td>3526</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>18.0</td>\n",
       "        <td>4207</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>19.0</td>\n",
       "        <td>5322</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>20.0</td>\n",
       "        <td>5986</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">31 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('Italy', 1.0, None),\n",
       " ('Italy', 2.0, 342),\n",
       " ('Italy', 3.0, 466),\n",
       " ('Italy', 4.0, 587),\n",
       " ('Italy', 5.0, 769),\n",
       " ('Italy', 6.0, 778),\n",
       " ('Italy', 7.0, 1247),\n",
       " ('Italy', 8.0, 1492),\n",
       " ('Italy', 9.0, 1797),\n",
       " ('Italy', 10.0, 977),\n",
       " ('Italy', 11.0, 2313),\n",
       " ('Italy', 12.0, 0),\n",
       " ('Italy', 13.0, 5198),\n",
       " ('Italy', 14.0, 3497),\n",
       " ('Italy', 15.0, 3590),\n",
       " ('Italy', 16.0, 3233),\n",
       " ('Italy', 17.0, 3526),\n",
       " ('Italy', 18.0, 4207),\n",
       " ('Italy', 19.0, 5322),\n",
       " ('Italy', 20.0, 5986),\n",
       " ('Italy', 21.0, 6557),\n",
       " ('Italy', 22.0, 5560),\n",
       " ('Italy', 23.0, 4789),\n",
       " ('Italy', 24.0, 5249),\n",
       " ('Italy', 25.0, 5210),\n",
       " ('Italy', 26.0, 6203),\n",
       " ('Italy', 27.0, 5909),\n",
       " ('Italy', 28.0, 5974),\n",
       " ('Italy', 29.0, 5217),\n",
       " ('Italy', 30.0, 4050),\n",
       " ('Italy', 31.0, 4053)]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, EXTRACT(DAY FROM whn), \n",
    "  confirmed - LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS new\n",
    "    FROM covid\n",
    "    WHERE name = 'Italy' AND EXTRACT(MONTH FROM whn) = 3\n",
    "    ORDER BY whn;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Weekly changes\n",
    "\n",
    "The data gathered are necessarily estimates and are inaccurate. However by taking a longer time span we can mitigate some of the effects.\n",
    "\n",
    "```sql\n",
    "SELECT name, DATE_FORMAT(whn,'%Y-%m-%d'), confirmed\n",
    " FROM covid\n",
    "WHERE name = 'Italy'\n",
    "AND WEEKDAY(whn) = 0\n",
    "ORDER BY whn\n",
    "```\n",
    "\n",
    "You can filter the data to view only Monday's figures **WHERE WEEKDAY(whn) = 0**.\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "13 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>to_char</th>\n",
       "        <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-01-26</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-02</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-09</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-16</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-23</td>\n",
       "        <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-01</td>\n",
       "        <td>1539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-08</td>\n",
       "        <td>5681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-15</td>\n",
       "        <td>17372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-22</td>\n",
       "        <td>34391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-29</td>\n",
       "        <td>38551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-05</td>\n",
       "        <td>31259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-12</td>\n",
       "        <td>27415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-19</td>\n",
       "        <td>22609</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Italy', '2020-01-26', None),\n",
       " ('Italy', '2020-02-02', 2),\n",
       " ('Italy', '2020-02-09', 1),\n",
       " ('Italy', '2020-02-16', 0),\n",
       " ('Italy', '2020-02-23', 152),\n",
       " ('Italy', '2020-03-01', 1539),\n",
       " ('Italy', '2020-03-08', 5681),\n",
       " ('Italy', '2020-03-15', 17372),\n",
       " ('Italy', '2020-03-22', 34391),\n",
       " ('Italy', '2020-03-29', 38551),\n",
       " ('Italy', '2020-04-05', 31259),\n",
       " ('Italy', '2020-04-12', 27415),\n",
       " ('Italy', '2020-04-19', 22609)]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT name, TO_CHAR(whn, 'yyyy-mm-dd'), \n",
    "  confirmed-LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY whn) AS new\n",
    "    FROM covid\n",
    "    WHERE name = 'Italy' AND EXTRACT(DOW FROM whn) = 0\n",
    "    ORDER BY whn;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. LAG using a JOIN\n",
    "\n",
    "You can JOIN a table using DATE arithmetic. This will give different results if data is missing.\n",
    "\n",
    "```sql\n",
    "SELECT tw.name, DATE_FORMAT(tw.whn,'%Y-%m-%d'), \n",
    " tw.confirmed, lw.confirmed\n",
    " FROM covid tw LEFT JOIN covid lw ON \n",
    "  DATE_ADD(lw.whn, INTERVAL 1 WEEK) = tw.whn\n",
    "   AND tw.name=lw.name\n",
    "WHERE tw.name = 'Italy'\n",
    "ORDER BY tw.whn\n",
    "```\n",
    "\n",
    "**Show the number of new cases in Italy for each week - show Monday only.**\n",
    "\n",
    "In the sample query we JOIN this week tw with last week lw using the DATE_ADD function."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "13 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>to_char</th>\n",
       "        <th>new</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-01-26</td>\n",
       "        <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-02</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-09</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-16</td>\n",
       "        <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-02-23</td>\n",
       "        <td>152</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-01</td>\n",
       "        <td>1539</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-08</td>\n",
       "        <td>5681</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-15</td>\n",
       "        <td>17372</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-22</td>\n",
       "        <td>34391</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-29</td>\n",
       "        <td>38551</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-05</td>\n",
       "        <td>31259</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-12</td>\n",
       "        <td>27415</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-04-19</td>\n",
       "        <td>22609</td>\n",
       "    </tr>\n",
       "</table>"
      ],
      "text/plain": [
       "[('Italy', '2020-01-26', None),\n",
       " ('Italy', '2020-02-02', 2),\n",
       " ('Italy', '2020-02-09', 1),\n",
       " ('Italy', '2020-02-16', 0),\n",
       " ('Italy', '2020-02-23', 152),\n",
       " ('Italy', '2020-03-01', 1539),\n",
       " ('Italy', '2020-03-08', 5681),\n",
       " ('Italy', '2020-03-15', 17372),\n",
       " ('Italy', '2020-03-22', 34391),\n",
       " ('Italy', '2020-03-29', 38551),\n",
       " ('Italy', '2020-04-05', 31259),\n",
       " ('Italy', '2020-04-12', 27415),\n",
       " ('Italy', '2020-04-19', 22609)]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT tw.name, TO_CHAR(tw.whn,'yyyy-mm-dd'), \n",
    "  tw.confirmed - lw.confirmed AS new\n",
    "    FROM covid tw LEFT JOIN covid lw ON \n",
    "    lw.whn + INTERVAL '1w' = tw.whn AND tw.name=lw.name\n",
    "    WHERE tw.name = 'Italy' AND EXTRACT(DOW FROM tw.whn)=0\n",
    "    ORDER BY tw.whn;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. RANK()\n",
    "\n",
    "The query shown shows the number of confirmed cases together with the world ranking for cases.\n",
    "\n",
    "United States has the highest number, Spain is number 2...\n",
    "\n",
    "Notice that while Spain has the second highest confirmed cases, Italy has the second highest number of deaths due to the virus.\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "   name,\n",
    "   confirmed,\n",
    "   RANK() OVER (ORDER BY confirmed DESC) rc,\n",
    "   deaths\n",
    "  FROM covid\n",
    "WHERE whn = '2020-04-20'\n",
    "ORDER BY confirmed DESC\n",
    "```\n",
    "\n",
    "**Include the ranking for the number of deaths in the table. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "90 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>confirmed</th>\n",
       "        <th>rc</th>\n",
       "        <th>deaths</th>\n",
       "        <th>rc2</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>784326</td>\n",
       "        <td>1</td>\n",
       "        <td>42094</td>\n",
       "        <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>200210</td>\n",
       "        <td>2</td>\n",
       "        <td>20852</td>\n",
       "        <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>181228</td>\n",
       "        <td>3</td>\n",
       "        <td>24114</td>\n",
       "        <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>156480</td>\n",
       "        <td>4</td>\n",
       "        <td>20292</td>\n",
       "        <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>147065</td>\n",
       "        <td>5</td>\n",
       "        <td>4862</td>\n",
       "        <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>125856</td>\n",
       "        <td>6</td>\n",
       "        <td>16550</td>\n",
       "        <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>90980</td>\n",
       "        <td>7</td>\n",
       "        <td>2140</td>\n",
       "        <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>83817</td>\n",
       "        <td>8</td>\n",
       "        <td>4636</td>\n",
       "        <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "        <td>83505</td>\n",
       "        <td>9</td>\n",
       "        <td>5209</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>47121</td>\n",
       "        <td>10</td>\n",
       "        <td>405</td>\n",
       "        <td>23</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>40743</td>\n",
       "        <td>11</td>\n",
       "        <td>2587</td>\n",
       "        <td>11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>39983</td>\n",
       "        <td>12</td>\n",
       "        <td>5828</td>\n",
       "        <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>37657</td>\n",
       "        <td>13</td>\n",
       "        <td>1725</td>\n",
       "        <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Netherlands</td>\n",
       "        <td>33588</td>\n",
       "        <td>14</td>\n",
       "        <td>3764</td>\n",
       "        <td>10</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>20863</td>\n",
       "        <td>15</td>\n",
       "        <td>735</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>18539</td>\n",
       "        <td>16</td>\n",
       "        <td>592</td>\n",
       "        <td>17</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>16325</td>\n",
       "        <td>17</td>\n",
       "        <td>445</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Sweden</td>\n",
       "        <td>14777</td>\n",
       "        <td>18</td>\n",
       "        <td>1580</td>\n",
       "        <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>10797</td>\n",
       "        <td>19</td>\n",
       "        <td>236</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>South Korea</td>\n",
       "        <td>10674</td>\n",
       "        <td>20</td>\n",
       "        <td>236</td>\n",
       "        <td>27</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">90 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('United States', 784326, 1, 42094, 1),\n",
       " ('Spain', 200210, 2, 20852, 3),\n",
       " ('Italy', 181228, 3, 24114, 2),\n",
       " ('France', 156480, 4, 20292, 4),\n",
       " ('Germany', 147065, 5, 4862, 8),\n",
       " ('United Kingdom', 125856, 6, 16550, 5),\n",
       " ('Turkey', 90980, 7, 2140, 12),\n",
       " ('China', 83817, 8, 4636, 9),\n",
       " ('Iran', 83505, 9, 5209, 7),\n",
       " ('Russia', 47121, 10, 405, 23),\n",
       " ('Brazil', 40743, 11, 2587, 11),\n",
       " ('Belgium', 39983, 12, 5828, 6),\n",
       " ('Canada', 37657, 13, 1725, 13),\n",
       " ('Netherlands', 33588, 14, 3764, 10),\n",
       " ('Portugal', 20863, 15, 735, 15),\n",
       " ('India', 18539, 16, 592, 17),\n",
       " ('Peru', 16325, 17, 445, 21),\n",
       " ('Sweden', 14777, 18, 1580, 14),\n",
       " ('Japan', 10797, 19, 236, 27),\n",
       " ('South Korea', 10674, 20, 236, 27),\n",
       " ('Chile', 10507, 21, 139, 35),\n",
       " ('Saudi Arabia', 10484, 22, 103, 38),\n",
       " ('Ecuador', 10128, 23, 507, 19),\n",
       " ('Poland', 9593, 24, 380, 25),\n",
       " ('Romania', 8936, 25, 478, 20),\n",
       " ('Pakistan', 8418, 26, 176, 32),\n",
       " ('Mexico', 8261, 27, 686, 16),\n",
       " ('Czech Republic', 6900, 28, 194, 30),\n",
       " ('Indonesia', 6760, 29, 590, 18),\n",
       " ('Australia', 6547, 30, 67, 42),\n",
       " ('Philippines', 6459, 31, 428, 22),\n",
       " ('Ukraine', 5710, 32, 151, 33),\n",
       " ('Malaysia', 5425, 33, 89, 40),\n",
       " ('Dominican Republic', 4964, 34, 235, 29),\n",
       " ('Colombia', 3977, 35, 189, 31),\n",
       " ('Egypt', 3333, 36, 250, 26),\n",
       " ('South Africa', 3300, 37, 58, 43),\n",
       " ('Morocco', 3046, 38, 143, 34),\n",
       " ('Bangladesh', 2948, 39, 101, 39),\n",
       " ('Argentina', 2941, 40, 136, 36),\n",
       " ('Thailand', 2792, 41, 47, 44),\n",
       " ('Algeria', 2718, 42, 384, 24),\n",
       " ('Greece', 2245, 43, 116, 37),\n",
       " ('Kazakhstan', 1852, 44, 19, 54),\n",
       " ('Uzbekistan', 1627, 45, 5, 68),\n",
       " ('Iraq', 1574, 46, 82, 41),\n",
       " ('Azerbaijan', 1436, 47, 19, 54),\n",
       " ('Cameroon', 1163, 48, 42, 45),\n",
       " ('Cuba', 1087, 49, 36, 48),\n",
       " ('Ghana', 1042, 50, 9, 60),\n",
       " ('Afghanistan', 1026, 51, 36, 48),\n",
       " ('Tunisia', 884, 52, 38, 46),\n",
       " (\"Côte d'Ivoire\", 847, 53, 9, 60),\n",
       " ('Nigeria', 665, 54, 22, 52),\n",
       " ('Niger', 648, 55, 20, 53),\n",
       " ('Guinea', 622, 56, 5, 68),\n",
       " ('Burkina Faso', 581, 57, 38, 46),\n",
       " ('Bolivia', 564, 58, 33, 50),\n",
       " ('Jordan', 425, 59, 7, 64),\n",
       " ('Taiwan', 422, 60, 6, 67),\n",
       " ('Senegal', 377, 61, 5, 68),\n",
       " ('Congo, Democratic Republic of', 332, 62, 25, 51),\n",
       " ('Sri Lanka', 304, 63, 7, 64),\n",
       " ('Guatemala', 289, 64, 7, 64),\n",
       " ('Kenya', 281, 65, 14, 56),\n",
       " ('Vietnam', 268, 66, 0, 81),\n",
       " ('Venezuela', 256, 67, 9, 60),\n",
       " ('Tanzania', 254, 68, 10, 59),\n",
       " ('Mali', 246, 69, 14, 56),\n",
       " ('Somalia', 237, 70, 8, 63),\n",
       " ('Rwanda', 147, 71, 0, 81),\n",
       " ('Cambodia', 122, 72, 0, 81),\n",
       " ('Madagascar', 121, 73, 0, 81),\n",
       " ('Myanmar', 119, 74, 5, 68),\n",
       " ('Ethiopia', 111, 75, 3, 72),\n",
       " ('Sudan', 107, 76, 12, 58),\n",
       " ('Zambia', 65, 77, 3, 72),\n",
       " ('Haiti', 57, 78, 3, 72),\n",
       " ('Uganda', 56, 79, 0, 81),\n",
       " ('Benin', 54, 80, 1, 79),\n",
       " ('Syria', 39, 81, 3, 72),\n",
       " ('Mozambique', 39, 81, 0, 81),\n",
       " ('Chad', 33, 83, 0, 81),\n",
       " ('Nepal', 31, 84, 0, 81),\n",
       " ('Zimbabwe', 25, 85, 3, 72),\n",
       " ('Angola', 24, 86, 2, 77),\n",
       " ('Malawi', 17, 87, 2, 77),\n",
       " ('Burundi', 5, 88, 1, 79),\n",
       " ('South Sudan', 4, 89, 0, 81),\n",
       " ('Yemen', 1, 90, 0, 81)]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "  covid.name, confirmed,\n",
    "    RANK() OVER (ORDER BY confirmed DESC) rc,\n",
    "    deaths, RANK() OVER (ORDER BY deaths DESC) rc2\n",
    "    FROM covid JOIN world ON (covid.name=world.name)\n",
    "    WHERE whn = '2020-04-20' AND population>=10000000\n",
    "    ORDER BY confirmed DESC;"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Infection rate\n",
    "\n",
    "The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).\n",
    "\n",
    "```sql\n",
    "SELECT \n",
    "   world.name,\n",
    "   ROUND(100000*confirmed/population,0)\n",
    "  FROM covid JOIN world ON covid.name=world.name\n",
    "WHERE whn = '2020-04-20' AND population > 10000000\n",
    "ORDER BY population DESC\n",
    "```\n",
    "\n",
    "**Show the infect rate ranking for each country. Only include countries with a population of at least 10 million.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "90 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>round</th>\n",
       "        <th>rank</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>6</td>\n",
       "        <td>52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>India</td>\n",
       "        <td>1</td>\n",
       "        <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>238</td>\n",
       "        <td>87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Indonesia</td>\n",
       "        <td>3</td>\n",
       "        <td>35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Pakistan</td>\n",
       "        <td>4</td>\n",
       "        <td>42</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>19</td>\n",
       "        <td>65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Nigeria</td>\n",
       "        <td>0</td>\n",
       "        <td>16</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Bangladesh</td>\n",
       "        <td>2</td>\n",
       "        <td>32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Russia</td>\n",
       "        <td>32</td>\n",
       "        <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Mexico</td>\n",
       "        <td>7</td>\n",
       "        <td>54</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Japan</td>\n",
       "        <td>9</td>\n",
       "        <td>59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Philippines</td>\n",
       "        <td>6</td>\n",
       "        <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Egypt</td>\n",
       "        <td>3</td>\n",
       "        <td>39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ethiopia</td>\n",
       "        <td>0</td>\n",
       "        <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Vietnam</td>\n",
       "        <td>0</td>\n",
       "        <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "        <td>100</td>\n",
       "        <td>79</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>109</td>\n",
       "        <td>80</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>177</td>\n",
       "        <td>82</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Congo, Democratic Republic of</td>\n",
       "        <td>0</td>\n",
       "        <td>21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>233</td>\n",
       "        <td>86</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">90 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('China', Decimal('6'), 52),\n",
       " ('India', Decimal('1'), 28),\n",
       " ('United States', Decimal('238'), 87),\n",
       " ('Indonesia', Decimal('3'), 35),\n",
       " ('Pakistan', Decimal('4'), 42),\n",
       " ('Brazil', Decimal('19'), 65),\n",
       " ('Nigeria', Decimal('0'), 16),\n",
       " ('Bangladesh', Decimal('2'), 32),\n",
       " ('Russia', Decimal('32'), 71),\n",
       " ('Mexico', Decimal('7'), 54),\n",
       " ('Japan', Decimal('9'), 59),\n",
       " ('Philippines', Decimal('6'), 51),\n",
       " ('Egypt', Decimal('3'), 39),\n",
       " ('Ethiopia', Decimal('0'), 7),\n",
       " ('Vietnam', Decimal('0'), 15),\n",
       " ('Iran', Decimal('100'), 79),\n",
       " ('Turkey', Decimal('109'), 80),\n",
       " ('Germany', Decimal('177'), 82),\n",
       " ('Congo, Democratic Republic of', Decimal('0'), 21),\n",
       " ('France', Decimal('233'), 86),\n",
       " ('Thailand', Decimal('4'), 45),\n",
       " ('United Kingdom', Decimal('189'), 83),\n",
       " ('Italy', Decimal('301'), 88),\n",
       " ('South Africa', Decimal('6'), 50),\n",
       " ('Tanzania', Decimal('0'), 18),\n",
       " ('Myanmar', Decimal('0'), 12),\n",
       " ('South Korea', Decimal('21'), 66),\n",
       " ('Colombia', Decimal('8'), 57),\n",
       " ('Kenya', Decimal('1'), 23),\n",
       " ('Spain', Decimal('425'), 90),\n",
       " ('Argentina', Decimal('7'), 55),\n",
       " ('Algeria', Decimal('6'), 53),\n",
       " ('Sudan', Decimal('0'), 14),\n",
       " ('Ukraine', Decimal('14'), 62),\n",
       " ('Uganda', Decimal('0'), 9),\n",
       " ('Iraq', Decimal('4'), 44),\n",
       " ('Poland', Decimal('25'), 68),\n",
       " ('Canada', Decimal('99'), 78),\n",
       " ('Morocco', Decimal('8'), 58),\n",
       " ('Saudi Arabia', Decimal('31'), 70),\n",
       " ('Uzbekistan', Decimal('5'), 47),\n",
       " ('Malaysia', Decimal('17'), 64),\n",
       " ('Afghanistan', Decimal('3'), 38),\n",
       " ('Venezuela', Decimal('1'), 24),\n",
       " ('Peru', Decimal('51'), 74),\n",
       " ('Angola', Decimal('0'), 4),\n",
       " ('Ghana', Decimal('3'), 40),\n",
       " ('Mozambique', Decimal('0'), 8),\n",
       " ('Nepal', Decimal('0'), 6),\n",
       " ('Yemen', Decimal('0'), 1),\n",
       " ('Cameroon', Decimal('4'), 46),\n",
       " ('Madagascar', Decimal('0'), 20),\n",
       " ('Australia', Decimal('25'), 69),\n",
       " (\"Côte d'Ivoire\", Decimal('4'), 41),\n",
       " ('Taiwan', Decimal('2'), 33),\n",
       " ('Niger', Decimal('3'), 37),\n",
       " ('Sri Lanka', Decimal('1'), 29),\n",
       " ('Burkina Faso', Decimal('3'), 36),\n",
       " ('Mali', Decimal('1'), 27),\n",
       " ('Romania', Decimal('46'), 72),\n",
       " ('Malawi', Decimal('0'), 5),\n",
       " ('Chile', Decimal('55'), 75),\n",
       " ('Kazakhstan', Decimal('10'), 61),\n",
       " ('Zambia', Decimal('0'), 17),\n",
       " ('Syria', Decimal('0'), 13),\n",
       " ('Ecuador', Decimal('58'), 76),\n",
       " ('Netherlands', Decimal('192'), 84),\n",
       " ('Guatemala', Decimal('2'), 31),\n",
       " ('Chad', Decimal('0'), 11),\n",
       " ('Senegal', Decimal('2'), 34),\n",
       " ('Somalia', Decimal('1'), 30),\n",
       " ('Cambodia', Decimal('1'), 25),\n",
       " ('Zimbabwe', Decimal('0'), 10),\n",
       " ('South Sudan', Decimal('0'), 2),\n",
       " ('Rwanda', Decimal('1'), 26),\n",
       " ('Guinea', Decimal('5'), 49),\n",
       " ('Benin', Decimal('0'), 19),\n",
       " ('Tunisia', Decimal('8'), 56),\n",
       " ('Haiti', Decimal('0'), 22),\n",
       " ('Belgium', Decimal('347'), 89),\n",
       " ('Bolivia', Decimal('5'), 48),\n",
       " ('Cuba', Decimal('10'), 60),\n",
       " ('Burundi', Decimal('0'), 3),\n",
       " ('Greece', Decimal('21'), 67),\n",
       " ('Czech Republic', Decimal('65'), 77),\n",
       " ('Jordan', Decimal('4'), 43),\n",
       " ('Dominican Republic', Decimal('48'), 73),\n",
       " ('Sweden', Decimal('143'), 81),\n",
       " ('Portugal', Decimal('203'), 85),\n",
       " ('Azerbaijan', Decimal('14'), 63)]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "SELECT \n",
    "   world.name,\n",
    "    ROUND(100000*confirmed/population,0),\n",
    "    RANK() OVER (ORDER BY confirmed/population)\n",
    "    FROM covid JOIN world ON covid.name=world.name\n",
    "    WHERE whn = '2020-04-20' AND population > 10000000\n",
    "    ORDER BY population DESC"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. Turning the corner\n",
    "\n",
    "For each country that has had at last 1000 new cases in a single day, show the date of the peak number of new cases."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      " * postgresql://postgres:***@localhost/sqlzoo\n",
      "26 rows affected.\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<table>\n",
       "    <tr>\n",
       "        <th>name</th>\n",
       "        <th>whn</th>\n",
       "        <th>new_cases</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>China</td>\n",
       "        <td>2020-02-13</td>\n",
       "        <td>15136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Italy</td>\n",
       "        <td>2020-03-21</td>\n",
       "        <td>6557</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Switzerland</td>\n",
       "        <td>2020-03-23</td>\n",
       "        <td>1321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Israel</td>\n",
       "        <td>2020-03-25</td>\n",
       "        <td>1131</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Spain</td>\n",
       "        <td>2020-03-25</td>\n",
       "        <td>9630</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Austria</td>\n",
       "        <td>2020-03-26</td>\n",
       "        <td>1321</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Germany</td>\n",
       "        <td>2020-03-27</td>\n",
       "        <td>6933</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Iran</td>\n",
       "        <td>2020-03-30</td>\n",
       "        <td>3186</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Canada</td>\n",
       "        <td>2020-04-05</td>\n",
       "        <td>2778</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ecuador</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>2196</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Ireland</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>1515</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Netherlands</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>1346</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Portugal</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>1516</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United Kingdom</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>8733</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>United States</td>\n",
       "        <td>2020-04-10</td>\n",
       "        <td>33755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Turkey</td>\n",
       "        <td>2020-04-11</td>\n",
       "        <td>5138</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>France</td>\n",
       "        <td>2020-04-12</td>\n",
       "        <td>26849</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Peru</td>\n",
       "        <td>2020-04-13</td>\n",
       "        <td>2265</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Belgium</td>\n",
       "        <td>2020-04-15</td>\n",
       "        <td>2454</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "        <td>Brazil</td>\n",
       "        <td>2020-04-17</td>\n",
       "        <td>3257</td>\n",
       "    </tr>\n",
       "</table>\n",
       "<span style=\"font-style:italic;text-align:center;\">26 rows, truncated to displaylimit of 20</span>"
      ],
      "text/plain": [
       "[('China', datetime.date(2020, 2, 13), 15136),\n",
       " ('Italy', datetime.date(2020, 3, 21), 6557),\n",
       " ('Switzerland', datetime.date(2020, 3, 23), 1321),\n",
       " ('Israel', datetime.date(2020, 3, 25), 1131),\n",
       " ('Spain', datetime.date(2020, 3, 25), 9630),\n",
       " ('Austria', datetime.date(2020, 3, 26), 1321),\n",
       " ('Germany', datetime.date(2020, 3, 27), 6933),\n",
       " ('Iran', datetime.date(2020, 3, 30), 3186),\n",
       " ('Canada', datetime.date(2020, 4, 5), 2778),\n",
       " ('Ecuador', datetime.date(2020, 4, 10), 2196),\n",
       " ('Ireland', datetime.date(2020, 4, 10), 1515),\n",
       " ('Netherlands', datetime.date(2020, 4, 10), 1346),\n",
       " ('Portugal', datetime.date(2020, 4, 10), 1516),\n",
       " ('United Kingdom', datetime.date(2020, 4, 10), 8733),\n",
       " ('United States', datetime.date(2020, 4, 10), 33755),\n",
       " ('Turkey', datetime.date(2020, 4, 11), 5138),\n",
       " ('France', datetime.date(2020, 4, 12), 26849),\n",
       " ('Peru', datetime.date(2020, 4, 13), 2265),\n",
       " ('Belgium', datetime.date(2020, 4, 15), 2454),\n",
       " ('Brazil', datetime.date(2020, 4, 17), 3257),\n",
       " ('Japan', datetime.date(2020, 4, 17), 1161),\n",
       " ('Saudi Arabia', datetime.date(2020, 4, 18), 1132),\n",
       " ('India', datetime.date(2020, 4, 19), 1893),\n",
       " ('Russia', datetime.date(2020, 4, 19), 6060),\n",
       " ('Belarus', datetime.date(2020, 4, 20), 1485),\n",
       " ('Singapore', datetime.date(2020, 4, 20), 1426)]"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%sql\n",
    "WITH t AS (\n",
    "    SELECT name, whn,\n",
    "       COALESCE(confirmed-LAG(confirmed, 1) OVER \n",
    "                (PARTITION BY name ORDER BY whn), 0) AS new_cases\n",
    "    FROM covid\n",
    "), r AS (\n",
    "    SELECT name, whn, new_cases,\n",
    "       RANK() OVER (PARTITION BY name ORDER BY new_cases DESC) AS rank_new\n",
    "    FROM t\n",
    ")\n",
    "SELECT name, whn, new_cases FROM r\n",
    "    WHERE rank_new=1 AND new_cases>1000\n",
    "    ORDER BY whn, name;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
